import xlwings as xw

def write_excel(file_path, sheet_name='Sheet1', datas=[]):
    """
        file_path: 文件路径
        sheet_name: sheet名称
        datas: 数据, 列表，如[1,2,3,4]; 二维列表，如[[1,2],[3,4]]
    """
    app = xw.App(visible=False, add_book=False)
    wb = app.books.add()
    try:
        sht = wb.sheets[0]
        sht.name = sheet_name

        sht.range('A1').expand().value = datas

        wb.save(file_path)

    finally:
        wb.close()
        app.quit()

def read_excel(file_path, row_count=-1, column_count=-1, row_number=-1, column_number=-1):
    """ 
        方法描述: 读取Excel数据
        参数说明:
        file_path: 文件路径
        row_count: 读取总行数
        column_count: 读取总列数
        row_number: 读取指定行数据
        column_number: 读取指定列数据


        # 读取sheet
        sht = wb.sheets['Sheet1']
        # sht = wb.sheets[0]

        # 获取数据行数
        # row_count = sht.used_range.last_cell.row
        row_count = sht.range('A1').expand('table').rows.count
        print('数据行数: %s' % row_count)

        # 获取数据列数
        # column_count = sht.used_range.last_cell.column
        column_count = sht.range('A1').expand('table').columns.count
        print('数据列数: %s' % column_count)

        # 读取指定单元格数据
        # range_value = sht.range('A2').value
        range_value = sht.range(2, 1).value
        print('单元格数据: %s' % range_value)

        # 读取行数据
        row_values = sht.range('A1').expand('right').value
        print('行数据: %s' % row_values)

        # 读取列数据
        column_values = sht.range('A1').expand('down').value
        print('列数据: %s' % column_values)

        # 读取指定区域数据
        # range_values = sht.range('A1:B3').value
        range_values = sht.range((1, 1), (3, 2)).value
        # range_values = sht[:3, :2].value
        print('区域数据: %s' % range_values)

        # 读取全部内容
        all_values = sht.range('A1').expand('table').value
        print('全部数据: %s' % all_values)
    """
    app = xw.App(visible=False, add_book=False)
    wb = app.books.open(file_path)

    try:
        # 读取sheet
        sht = wb.sheets[0]

        if row_count > 0 and column_count < 0:
            return sht[:row_count, :sht.range('A1').expand('table').columns.count].value
        elif row_count < 0 and column_count > 0:
            return sht[:sht.range('A1').expand('table').rows.count, :column_count].value
        elif row_number > 0:
            return sht.range(row_number, 1).expand('right').value
        elif column_number > 0:
            return sht.range(1, column_number).expand('down').value
        else:
            # 读取全部内容
            return sht.range('A1').expand('table').value

    finally:
        wb.close()
        app.quit()


if __name__ == '__main__':
    
    # datas = read_excel("C:/Users/lyon.lu/Desktop/test.xlsx")

    # write_excel("C:/Users/lyon.lu/Desktop/test1.xlsx", sheet_name='sheet2', datas=datas)